Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Minimizing Free List Contention

One other area of possible contention is in the free list. The free list is maintained to provide a faster mechanism to get free data blocks from the buffer cache. The free list contains a linked list of blocks in the segment that has space available.

Contention on the free list can be determined by looking at the dynamic performance table, V$WAITSTAT. Use the following SQL statement to obtain this information:

SQL> SELECT class, count
  2  FROM v$waitstat
  3  WHERE class = 'free list'

CLASS                  COUNT
------------------  --------
free list                  0

If the free list number is greater than 1 percent of the total number of requests, you should add more free lists. Remember that the total number of requests is determined from the sum of database block gets and consistent gets from V$SYSSTAT as shown here:

SQL> SELECT SUM(value) "Data Requests"
  2  FROM v$sysstat
  3  WHERE name IN ('db block gets', 'consistent gets');
Data Requests
------------
         5105

You can add more free lists by re-creating the table with a larger value for the FREELISTS storage parameter. You may want to increase this parameter to the number of concurrent INSERT transactions that you expect to see.

Although there usually is not a problem with free lists, monitoring them periodically will alert you if the system load has increased to where free lists are a problem.

Summary

This chapter looked at several areas you can improve in the Oracle instance. Most of these areas have a general theme; this theme relates to the fact that disk I/O is many times slower than memory.

If you can optimize an instance to take advantage of this fact, you can achieve optimal performance. Here is a summary of the goals discussed in this chapter:

  Tune for cache hits. By sizing DB_BLOCK_BUFFERS and SHARED_POOL_SIZE to get good cache-hit rates, I/Os are reduced.
  Separate sequential and random I/O. Doing so speeds up access to disk and optimizes the disk subsystem.
  Avoid unnecessary I/Os. Reduce dynamic space allocation. Also reduce chained and migrated rows.
  Minimize contention. This is true for rollback segments, free lists, and redo buffer latches.
  Sort in memory. If you have memory available, allow sorts to occur in memory, thus increasing sort performance and reducing I/Os.
  Tune archiving. You should always archive. Tune the archive process to have the least effect on your system.
  Tune checkpoints. Adjust the checkpoint interval based on your needs (remember that there is a performance-versus-recovery interval issue here).

These areas of instance tuning can all help the overall performance of the system. The next chapter looks at some additional areas where you can change the system to increase performance.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.